package zgrjb.snbq.teach_platform.service;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import jakarta.annotation.PostConstruct;
import java.util.List;
import java.util.Map;

/**
 * 数据库初始化服务
 * 用于执行测试数据和初始化脚本
 */
@Service
@Slf4j
public class DatabaseInitService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 应用启动时执行初始化
     */
    @PostConstruct
    public void init() {
        log.info("开始执行数据库初始化...");
        
        // 检查是否需要初始化消息数据
        if (needInitMessageData()) {
            initMessageTestData();
        }
        
        log.info("数据库初始化完成");
    }

    /**
     * 检查是否需要初始化消息数据
     */
    private boolean needInitMessageData() {
        try {
            String sql = "SELECT COUNT(*) FROM conversation";
            Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
            return count == null || count == 0;
        } catch (Exception e) {
            log.warn("检查消息数据时出错，将执行初始化: {}", e.getMessage());
            return true;
        }
    }

    /**
     * 初始化消息测试数据
     */
    @Transactional
    public void initMessageTestData() {
        log.info("开始初始化消息测试数据...");
        
        try {
            // 1. 创建测试会话
            createTestConversations();
            
            // 2. 创建测试消息
            createTestMessages();
            
            // 3. 更新会话的最后消息信息
            updateConversationLastMessages();
            
            // 4. 创建测试通知
            createTestNotifications();
            
            log.info("消息测试数据初始化完成");
            
        } catch (Exception e) {
            log.error("初始化消息测试数据失败", e);
            throw new RuntimeException("初始化消息测试数据失败", e);
        }
    }

    /**
     * 创建测试会话
     */
    private void createTestConversations() {
        log.info("创建测试会话...");
        
        String sql = """
            INSERT INTO `conversation` (
                `id`, `type`, `title`, `avatar`, `participants`, `creator_id`, 
                `is_active`, `create_time`, `update_time`
            ) VALUES 
            ('conv_2001_3001', 'private', '张教授', 'https://img.icons8.com/color/96/teacher.png', 
             '[2001,3001]', 2001, 1, NOW(), NOW()),
            ('conv_2002_3002', 'private', '李副教授', 'https://img.icons8.com/color/96/teacher.png',
             '[2002,3002]', 2002, 1, NOW(), NOW()),
            ('conv_2003_3003', 'private', '王讲师', 'https://img.icons8.com/color/96/teacher.png',
             '[2003,3003]', 2003, 1, NOW(), NOW()),
            ('conv_3001_3002', 'private', '李明轩', 'https://img.icons8.com/color/96/student-male.png',
             '[3001,3002]', 3001, 1, NOW(), NOW()),
            ('conv_3001_3003', 'private', '李明轩', 'https://img.icons8.com/color/96/student-male.png',
             '[3001,3003]', 3001, 1, NOW(), NOW()),
            ('conv_2001_2002', 'private', '张教授', 'https://img.icons8.com/color/96/teacher.png',
             '[2001,2002]', 2001, 1, NOW(), NOW())
            ON DUPLICATE KEY UPDATE `update_time` = NOW()
            """;
        
        jdbcTemplate.execute(sql);
        log.info("测试会话创建完成");
    }

    /**
     * 创建测试消息
     */
    private void createTestMessages() {
        log.info("创建测试消息...");
        
        String sql = """
            INSERT INTO `message` (
                `id`, `from_user_id`, `to_user_id`, `message_type`, `content`, `content_type`,
                `conversation_id`, `is_read`, `is_deleted`, `priority`, `create_time`, `update_time`
            ) VALUES 
            (7001, 2001, 3001, 'private', '李明轩同学，你的数据结构作业完成得不错！', 'text', 'conv_2001_3001', 1, 0, 1, DATE_SUB(NOW(), INTERVAL 2 DAY), DATE_SUB(NOW(), INTERVAL 2 DAY)),
            (7002, 3001, 2001, 'private', '谢谢张教授！我会继续努力的。', 'text', 'conv_2001_3001', 1, 0, 1, DATE_SUB(NOW(), INTERVAL 1 DAY), DATE_SUB(NOW(), INTERVAL 1 DAY)),
            (7003, 2001, 3001, 'private', '下周的考试要好好准备，有什么问题随时问我。', 'text', 'conv_2001_3001', 0, 0, 2, DATE_SUB(NOW(), INTERVAL 6 HOUR), DATE_SUB(NOW(), INTERVAL 6 HOUR)),
            (7004, 2002, 3002, 'private', '王雨涵，软件工程项目的进度如何？', 'text', 'conv_2002_3002', 1, 0, 1, DATE_SUB(NOW(), INTERVAL 3 DAY), DATE_SUB(NOW(), INTERVAL 3 DAY)),
            (7005, 3002, 2002, 'private', '李老师，项目已经完成了80%，预计下周可以提交。', 'text', 'conv_2002_3002', 1, 0, 1, DATE_SUB(NOW(), INTERVAL 2 DAY), DATE_SUB(NOW(), INTERVAL 2 DAY)),
            (7006, 2002, 3002, 'private', '很好！记得做好测试和文档。', 'text', 'conv_2002_3002', 0, 0, 1, DATE_SUB(NOW(), INTERVAL 4 HOUR), DATE_SUB(NOW(), INTERVAL 4 HOUR)),
            (7007, 2003, 3003, 'private', '张浩然，算法作业有困难吗？', 'text', 'conv_2003_3003', 1, 0, 1, DATE_SUB(NOW(), INTERVAL 1 DAY), DATE_SUB(NOW(), INTERVAL 1 DAY)),
            (7008, 3003, 2003, 'private', '王老师，动态规划部分有点难理解。', 'text', 'conv_2003_3003', 1, 0, 1, DATE_SUB(NOW(), INTERVAL 12 HOUR), DATE_SUB(NOW(), INTERVAL 12 HOUR)),
            (7009, 2003, 3003, 'private', '明天下午我有时间，可以单独辅导你。', 'text', 'conv_2003_3003', 0, 0, 2, DATE_SUB(NOW(), INTERVAL 2 HOUR), DATE_SUB(NOW(), INTERVAL 2 HOUR)),
            (7010, 3001, 3002, 'private', '王雨涵，数据结构作业写完了吗？', 'text', 'conv_3001_3002', 1, 0, 1, DATE_SUB(NOW(), INTERVAL 1 DAY), DATE_SUB(NOW(), INTERVAL 1 DAY)),
            (7011, 3002, 3001, 'private', '写完了，你呢？', 'text', 'conv_3001_3002', 1, 0, 1, DATE_SUB(NOW(), INTERVAL 12 HOUR), DATE_SUB(NOW(), INTERVAL 12 HOUR)),
            (7012, 3001, 3002, 'private', '我也写完了，要不要一起复习一下？', 'text', 'conv_3001_3002', 0, 0, 1, DATE_SUB(NOW(), INTERVAL 3 HOUR), DATE_SUB(NOW(), INTERVAL 3 HOUR)),
            (7013, 3001, 3003, 'private', '张浩然，明天一起去图书馆学习吗？', 'text', 'conv_3001_3003', 1, 0, 1, DATE_SUB(NOW(), INTERVAL 2 DAY), DATE_SUB(NOW(), INTERVAL 2 DAY)),
            (7014, 3003, 3001, 'private', '好啊，几点？', 'text', 'conv_3001_3003', 1, 0, 1, DATE_SUB(NOW(), INTERVAL 1 DAY), DATE_SUB(NOW(), INTERVAL 1 DAY)),
            (7015, 3001, 3003, 'private', '上午9点，图书馆二楼。', 'text', 'conv_3001_3003', 0, 0, 1, DATE_SUB(NOW(), INTERVAL 5 HOUR), DATE_SUB(NOW(), INTERVAL 5 HOUR)),
            (7016, 2001, 2002, 'private', '李老师，下周的教学研讨会你参加吗？', 'text', 'conv_2001_2002', 1, 0, 1, DATE_SUB(NOW(), INTERVAL 1 DAY), DATE_SUB(NOW(), INTERVAL 1 DAY)),
            (7017, 2002, 2001, 'private', '参加的，张教授有什么建议吗？', 'text', 'conv_2001_2002', 1, 0, 1, DATE_SUB(NOW(), INTERVAL 12 HOUR), DATE_SUB(NOW(), INTERVAL 12 HOUR)),
            (7018, 2001, 2002, 'private', '我们可以讨论一下课程改革的问题。', 'text', 'conv_2001_2002', 0, 0, 1, DATE_SUB(NOW(), INTERVAL 1 HOUR), DATE_SUB(NOW(), INTERVAL 1 HOUR))
            ON DUPLICATE KEY UPDATE `update_time` = NOW()
            """;
        
        jdbcTemplate.execute(sql);
        log.info("测试消息创建完成");
    }

    /**
     * 更新会话的最后消息信息
     */
    private void updateConversationLastMessages() {
        log.info("更新会话的最后消息信息...");
        
        String[] updates = {
            "UPDATE `conversation` SET `last_message_id` = 7003, `last_message_time` = DATE_SUB(NOW(), INTERVAL 6 HOUR) WHERE `id` = 'conv_2001_3001'",
            "UPDATE `conversation` SET `last_message_id` = 7006, `last_message_time` = DATE_SUB(NOW(), INTERVAL 4 HOUR) WHERE `id` = 'conv_2002_3002'",
            "UPDATE `conversation` SET `last_message_id` = 7009, `last_message_time` = DATE_SUB(NOW(), INTERVAL 2 HOUR) WHERE `id` = 'conv_2003_3003'",
            "UPDATE `conversation` SET `last_message_id` = 7012, `last_message_time` = DATE_SUB(NOW(), INTERVAL 3 HOUR) WHERE `id` = 'conv_3001_3002'",
            "UPDATE `conversation` SET `last_message_id` = 7015, `last_message_time` = DATE_SUB(NOW(), INTERVAL 5 HOUR) WHERE `id` = 'conv_3001_3003'",
            "UPDATE `conversation` SET `last_message_id` = 7018, `last_message_time` = DATE_SUB(NOW(), INTERVAL 1 HOUR) WHERE `id` = 'conv_2001_2002'"
        };
        
        for (String update : updates) {
            jdbcTemplate.execute(update);
        }
        
        log.info("会话最后消息信息更新完成");
    }

    /**
     * 创建测试通知
     */
    private void createTestNotifications() {
        log.info("创建测试通知...");
        
        String sql = """
            INSERT INTO `notification` (
                `id`, `user_id`, `type`, `title`, `content`, `action_url`, 
                `is_read`, `priority`, `source_id`, `source_type`, `create_time`, `update_time`
            ) VALUES 
            (8001, 3001, 'exam', '数据结构期中考试通知', '数据结构与算法课程期中考试将于明天上午9点开始，请做好准备。', '/student/exams', 0, 2, 6001, 'exam', DATE_SUB(NOW(), INTERVAL 1 DAY), DATE_SUB(NOW(), INTERVAL 1 DAY)),
            (8002, 3002, 'exam', '软件工程期末考试通知', '软件工程课程期末考试将于下周一开始，请及时复习。', '/student/exams', 0, 2, 6002, 'exam', DATE_SUB(NOW(), INTERVAL 2 DAY), DATE_SUB(NOW(), INTERVAL 2 DAY)),
            (8003, 3003, 'assignment', '算法作业提交提醒', '算法作业截止日期为明天，请及时提交。', '/student/assignments', 0, 1, 5001, 'assignment', DATE_SUB(NOW(), INTERVAL 6 HOUR), DATE_SUB(NOW(), INTERVAL 6 HOUR)),
            (8004, 2001, 'system', '系统维护通知', '系统将于今晚22:00-24:00进行维护，期间可能无法正常访问。', '/teacher/dashboard', 0, 1, NULL, 'system', DATE_SUB(NOW(), INTERVAL 3 HOUR), DATE_SUB(NOW(), INTERVAL 3 HOUR)),
            (8005, 3001, 'system', '欢迎使用XHT教学平台', '欢迎李明轩同学使用XHT教学平台！如有问题请联系管理员。', '/student/home', 1, 1, NULL, 'system', DATE_SUB(NOW(), INTERVAL 7 DAY), DATE_SUB(NOW(), INTERVAL 7 DAY))
            ON DUPLICATE KEY UPDATE `update_time` = NOW()
            """;
        
        jdbcTemplate.execute(sql);
        log.info("测试通知创建完成");
    }

    /**
     * 获取数据统计信息
     */
    public Map<String, Object> getDataStats() {
        log.info("获取数据统计信息...");
        
        try {
            String conversationCount = "SELECT COUNT(*) FROM conversation";
            String messageCount = "SELECT COUNT(*) FROM message";
            String notificationCount = "SELECT COUNT(*) FROM notification";
            String unreadMessageCount = "SELECT COUNT(*) FROM message WHERE is_read = 0";
            String unreadNotificationCount = "SELECT COUNT(*) FROM notification WHERE is_read = 0";
            
            Integer convCount = jdbcTemplate.queryForObject(conversationCount, Integer.class);
            Integer msgCount = jdbcTemplate.queryForObject(messageCount, Integer.class);
            Integer notifCount = jdbcTemplate.queryForObject(notificationCount, Integer.class);
            Integer unreadMsgCount = jdbcTemplate.queryForObject(unreadMessageCount, Integer.class);
            Integer unreadNotifCount = jdbcTemplate.queryForObject(unreadNotificationCount, Integer.class);
            
            Map<String, Object> stats = Map.of(
                "conversationCount", convCount != null ? convCount : 0,
                "messageCount", msgCount != null ? msgCount : 0,
                "notificationCount", notifCount != null ? notifCount : 0,
                "unreadMessageCount", unreadMsgCount != null ? unreadMsgCount : 0,
                "unreadNotificationCount", unreadNotifCount != null ? unreadNotifCount : 0
            );
            
            log.info("数据统计信息: {}", stats);
            return stats;
            
        } catch (Exception e) {
            log.error("获取数据统计信息失败", e);
            return Map.of(
                "conversationCount", 0,
                "messageCount", 0,
                "notificationCount", 0,
                "unreadMessageCount", 0,
                "unreadNotificationCount", 0,
                "error", e.getMessage()
            );
        }
    }

    /**
     * 手动触发初始化（用于测试）
     */
    @Transactional
    public void manualInit() {
        log.info("手动触发数据库初始化...");
        initMessageTestData();
    }
} 